package com.kcs.fxmargin.report.utils;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Font;

public class ReportExcelUtil {
    
    public static final String CONTENT_TYPE_XLSX = ".xlsx";
    public static final String CONTENT_TYPE_XLS = ".xls";
        
    public static final HSSFColor BG_COLOR_AQUA =  new HSSFColor.AQUA();
    public static final HSSFColor BG_COLOR_WHITE =  new HSSFColor.WHITE();
    public static final HSSFColor BG_COLOR_LIME=  new HSSFColor.LIME();
    public static final HSSFColor BG_COLOR_BLACK=  new HSSFColor.BLACK();
    
    public static final short ALIGN_LEFT   = HSSFCellStyle.ALIGN_LEFT;
    public static final short ALIGN_CENTER = HSSFCellStyle.ALIGN_CENTER;
    public static final short ALIGN_RIGHT  = HSSFCellStyle.ALIGN_RIGHT;

    public static final String FONT_NAME_DEFAULT = "Arial";
    public static final short FONT_SIZE_DEFAULT = 10;
    public static final short FONT_COLOR_DEFAULT = HSSFColor.BLACK.index;
   
    
    public static HSSFFont getDefaultFont(HSSFWorkbook wb){
        return getFont(wb, FONT_NAME_DEFAULT, FONT_SIZE_DEFAULT, FONT_COLOR_DEFAULT, Font.BOLDWEIGHT_NORMAL);
    }
    public static HSSFFont getDefaultFontBold(HSSFWorkbook wb){
        return getFont(wb, FONT_NAME_DEFAULT, FONT_SIZE_DEFAULT, FONT_COLOR_DEFAULT, Font.BOLDWEIGHT_BOLD);
    }
    public static HSSFFont getDefaultFont(HSSFWorkbook wb, short color){
        return getFont(wb, FONT_NAME_DEFAULT, FONT_SIZE_DEFAULT, color, Font.BOLDWEIGHT_NORMAL);
    }
    public static HSSFFont getDefaultFontBold(HSSFWorkbook wb, short color){
        return getFont(wb, FONT_NAME_DEFAULT, FONT_SIZE_DEFAULT, color, Font.BOLDWEIGHT_BOLD);
    }
    public static HSSFFont getFont(HSSFWorkbook wb, short size, short color, short weight){
        return getFont(wb, FONT_NAME_DEFAULT, size, color, weight);
    }
    public static HSSFFont getDefaultFontHeader(HSSFWorkbook wb){
        return getFontHeader(wb, FONT_COLOR_DEFAULT);
    }
    public static HSSFFont getFontHeader(HSSFWorkbook wb, short color){
        return getFont(wb, FONT_NAME_DEFAULT, FONT_SIZE_DEFAULT, color, Font.BOLDWEIGHT_BOLD);
    }
    
    public static HSSFFont getFont(HSSFWorkbook wb,String fontName, short size, short color, short weight){
        HSSFFont font = wb.createFont();
        font.setFontName(fontName);
        font.setFontHeightInPoints(size);
        font.setColor(color);
        font.setBoldweight(weight);
        return font;
    }
    
    
    public static HSSFCellStyle getDefaultCellStyleNoBorder(HSSFWorkbook wb,boolean isWrap){
        return getDefaultCellStyle(wb, BG_COLOR_WHITE, getDefaultFont(wb), HSSFCellStyle.ALIGN_LEFT, false, isWrap);
    }
    public static HSSFCellStyle getDefaultCellStyleNoBorder(HSSFWorkbook wb, HSSFFont font, short align,boolean isWrap){
        return getDefaultCellStyle(wb, BG_COLOR_WHITE, font, align, false, isWrap);
    }
    public static HSSFCellStyle getDefaultCellStyle(HSSFWorkbook wb, short align,boolean isWrap){
        return getDefaultCellStyle(wb, BG_COLOR_WHITE, getDefaultFont(wb), align, true, isWrap);
    }
    public static HSSFCellStyle getDefaultCellStyle(HSSFWorkbook wb,boolean isWrap){
        return getDefaultCellStyle(wb, BG_COLOR_WHITE, getDefaultFont(wb), HSSFCellStyle.ALIGN_LEFT, true, isWrap);
    }
    public static HSSFCellStyle getDefaultCellStyle(HSSFWorkbook wb){
        return getDefaultCellStyle(wb, BG_COLOR_WHITE, getDefaultFont(wb), HSSFCellStyle.ALIGN_LEFT, true, true);
    }
    public static HSSFCellStyle getDefaultCellStyle(HSSFWorkbook wb, HSSFFont font,boolean isWrap){
        return getDefaultCellStyle(wb, BG_COLOR_WHITE, font, HSSFCellStyle.ALIGN_LEFT, true, isWrap);
    }
    public static HSSFCellStyle getDefaultCellStyle(HSSFWorkbook wb, HSSFFont font, short align,boolean isWrap){
        return getDefaultCellStyle(wb, BG_COLOR_WHITE, font, align, true,isWrap);
    }
    public static HSSFCellStyle getDefaultCellStyle(HSSFWorkbook wb,HSSFColor bgColor,boolean isWrap){
        return getDefaultCellStyle(wb, bgColor, getDefaultFont(wb), HSSFCellStyle.ALIGN_LEFT, true,isWrap);
    }
    public static HSSFCellStyle getDefaultCellStyle(HSSFWorkbook wb,HSSFColor bgColor, HSSFFont font, short align , boolean border,boolean isWrap){
       HSSFCellStyle cellStyle = wb.createCellStyle();
    	cellStyle.setFont(font);    
    	cellStyle.setAlignment(align);
    	cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    	cellStyle.setWrapText(isWrap);
    	cellStyle.setFillForegroundColor(bgColor.getIndex());
        if(border){
            cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        }
        return cellStyle;
    }
    
    public static HSSFCellStyle getDefaultCellCurrencyStyle(HSSFWorkbook wb,HSSFColor bgColor, HSSFFont font, short align , boolean border){
       HSSFCellStyle cellStyle = getDefaultCellStyle(wb, bgColor, font, align, border,true);
	   HSSFDataFormat hdf = wb.createDataFormat();
        cellStyle.setDataFormat(hdf.getFormat("#,##0.00"));
        return cellStyle;
    }
    
    public static HSSFCellStyle getDefaultCellCurrencyStyle(HSSFWorkbook wb, HSSFFont font,boolean isWarp){
       HSSFCellStyle cellStyle = getDefaultCellStyle(wb, font, ALIGN_RIGHT, isWarp);
       HSSFDataFormat hdf = wb.createDataFormat();
        cellStyle.setDataFormat(hdf.getFormat("#,##0.00"));
        return cellStyle;
    }
    
    public static HSSFCellStyle getDefaultCellCurrencyStyle(HSSFWorkbook wb,boolean isWarp){
       HSSFCellStyle cellStyle = getDefaultCellStyle(wb, ALIGN_RIGHT,isWarp);
       HSSFDataFormat hdf = wb.createDataFormat();
        cellStyle.setDataFormat(hdf.getFormat("#,##0.00"));
        return cellStyle;
    }
    
    public static HSSFCellStyle getDefaultCellCriteriaStyle(HSSFWorkbook wb,boolean isBorder,boolean isWrap){
        HSSFCellStyle cellStyle = wb.createCellStyle();
    	cellStyle.setFont(getDefaultFontHeader(wb));   
  	   	cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    	//cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    	//cellStyle.setFillForegroundColor(new HSSFColor.WHITE().getIndex());
		cellStyle.setWrapText(isWrap);
    	if(isBorder){
	    	cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    	cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    	cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    	cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	}
        return cellStyle;
    }
    
    public static HSSFCellStyle getDefaultHeaderTableStyle(HSSFWorkbook wb,boolean isBorder,boolean isWrap){
        HSSFCellStyle cellStyle = wb.createCellStyle();
    	cellStyle.setFont(getDefaultFontHeader(wb));    
    	cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);    	
    	cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    	cellStyle.setFillForegroundColor(new HSSFColor.AQUA().getIndex());
		cellStyle.setWrapText(isWrap);
    	if(isBorder){
	    	cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    	cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    	cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    	cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	}
        return cellStyle;
    }
    
    public static HSSFCell createBlankCell(HSSFWorkbook wb,HSSFRow row,boolean isWrap){
        int currentCol = row.getLastCellNum()>0?row.getLastCellNum():0;
        HSSFCell cell = row.createCell(currentCol);
        cell.setCellStyle(getDefaultCellStyle(wb,isWrap ));
        return cell;
    }
    
    public static HSSFCell createBlankCellNoBorder(HSSFWorkbook wb,HSSFRow row,boolean isWrap){
        int currentCol = row.getLastCellNum()>0?row.getLastCellNum():0;
        HSSFCell cell = row.createCell(currentCol);
        cell.setCellStyle(getDefaultCellStyle(wb, BG_COLOR_WHITE, getDefaultFont(wb), HSSFCellStyle.VERTICAL_CENTER, false,isWrap));
        return cell;
    }
    
    public static void setDefaultConfigSheet(HSSFSheet sheet, int maxIndexCol){
    	
        for(int i=0;i<=maxIndexCol;i++){
            sheet.autoSizeColumn((short)i,true);
        }

        sheet.setDefaultRowHeight((short)400);
        sheet.getPrintSetup().setLandscape(true);
        sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); 
        sheet.setMargin(HSSFSheet.LeftMargin, 0.5);
        sheet.setMargin(HSSFSheet.RightMargin, 0.5);
        sheet.setMargin(HSSFSheet.BottomMargin, 0.5);
        sheet.getPrintSetup().setFitWidth((short) 1);
    }
    
    public static void setDefaultConfigSheet(HSSFSheet sheet){
//        sheet.setDefaultRowHeight((short)350);
        sheet.getPrintSetup().setLandscape(true);
        sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); 
        sheet.setMargin(HSSFSheet.LeftMargin, 0.5);
        sheet.setMargin(HSSFSheet.RightMargin, 0.5);
        sheet.setMargin(HSSFSheet.BottomMargin, 0.5);
        sheet.getPrintSetup().setFitWidth((short) 1);
        sheet.getPrintSetup().setFitHeight((short) 0);
    }

 
}